﻿using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using Production.CommonLib.Models;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace Production.ApplicationLib.Queries
{
    public class TechnologyQueries : ITechnologyQueries
    {
        private readonly string? _defaultConnStr;

        public TechnologyQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<PaginatedResult<TechnologyInfoResponseDto>> QueryTechnologiesByPage(
            TechnologySearchRequestDto model)
        {
            IEnumerable<TechnologyInfoResponseDto>? data;
            int totalCount;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessType,
       t.ProductionType,
       t.Quality,
       t.StandardBomId,
       t.StandardBomCode,
       t.StandardBomName,
       t.Sort,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_TECHNOLOGY t where 1 = 1 ");

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<TechnologyInfoResponseDto>(query.SqlPage);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount);
            }

            return await PaginatedResult<TechnologyInfoResponseDto>.SuccessAsync(data.ToList(), totalCount, model.Page,
                model.Limit);
        }

        public async Task<Result<IList<TechnologyInfoResponseDto>>> QueryTechnologies(TechnologySearchRequestDto model)
        {
            IEnumerable<TechnologyInfoResponseDto>? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<TechnologyInfoResponseDto>(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessType,
       t.ProductionType,
       t.Quality,
       t.StandardBomId,
       t.StandardBomCode,
       t.StandardBomName,
       t.Sort,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_TECHNOLOGY t where 1 = 1 ");

            }

            return await Result<IList<TechnologyInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<Result<TechnologyInfoResponseDto>?> GetTechnology(int id)
        {
            TechnologyInfoResponseDto? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryFirstOrDefaultAsync<TechnologyInfoResponseDto>(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessType,
       t.ProductionType,
       t.Quality,
       t.StandardBomId,
       t.StandardBomCode,
       t.StandardBomName,
       t.Sort,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_TECHNOLOGY t where 1 = 1 and Id = @id ", new { id });

                if (data != null)
                {
                    data.Items = await db.QueryAsync<TechnologyDetailInfoResponseDto>(@"
select t.id,
       t.processid,
       t.processcode,
       t.processname,
       t.technologyid,
       t.sort,
       t.description,
       t.datastate,
       t.createdby,
       t.createdon,
       t.lastmodifiedby,
       t.lastmodifiedon,
       t.deletedby,
       t.deletedon,
       t.taketime
from MES_TECHNOLOGY_DETAIL t where 1 = 1 and technologyid = @technologyid", new { technologyid = data.Id });
                }
            }

            return await Result<TechnologyInfoResponseDto>.SuccessAsync(data);
        }
    }
}